// Copyright (c) Appeon. All rights reserved.
// Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. 
// You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Threading;
using System.Threading.Tasks;

namespace SnapObjects.Data
{
    internal class ModelSqlExecutor
    {
        private readonly DataContext _context;
        private readonly IAdoDbProvider _provider;

        public ModelSqlExecutor(DataContext context)
        {
            _context = context;
            _provider = context.AdoDbProviderFactory.GetAdoDbProvider();
        }

        public IList<TModel> Select<TModel>(
            string sqlText,
            IList<IAdoDbParameter> adoDbParameters)
        {
            return this.ExecuteSqlQuery<TModel>(sqlText, adoDbParameters);
        }

        public Task<IList<TModel>> SelectAsync<TModel>(
            string sqlText, IList<IAdoDbParameter> adoDbParameters, CancellationToken cancellationToken)
        {
            return this.ExecuteSqlQueryAsync<TModel>(sqlText, adoDbParameters, default, cancellationToken);
        }

        public IList<TModel> SelectPage<TModel>(
            string sqlText,
            int currentIndex,
            int pageSize,
            IList<IAdoDbParameter> adoDbParameters)
        {
            IList<TModel> result;

            _context.CurrentConnection.Open();

            try
            {
                var queryResult = _provider.ExecuteSqlQuery<TModel>(
                    sqlText, adoDbParameters, CommandBehavior.SequentialAccess);

                result = queryResult.GetList(currentIndex, pageSize);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                _context.CurrentConnection.Close();
            }

            return result;
        }

        public async Task<IList<TModel>> SelectPageAsync<TModel>(
            string sqlText,
            int currentIndex,
            int pageSize,
            IList<IAdoDbParameter> adoDbParameters,
            CancellationToken cancellationToken)
        {
            await _context.CurrentConnection.OpenAsync(cancellationToken);

            try
            {
                var queryResult = await _provider.ExecuteSqlQueryAsync<TModel>(
                    sqlText, adoDbParameters, CommandBehavior.SequentialAccess);

                return await queryResult.GetListAsync(currentIndex, pageSize, cancellationToken);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                await _context.CurrentConnection.CloseAsync();
            }
        }

        public TModel SelectOneByKey<TModel>(
            string sqlText,
            IList<IAdoDbParameter> adoDbParameters)
        {
            var result = this.ExecuteSqlQuery<TModel>(sqlText, adoDbParameters, CommandBehavior.SingleRow);

            return result.FirstOrDefault();
        }

        public async Task<TModel> SelectOneByKeyAsync<TModel>(
            string sqlText,
            IList<IAdoDbParameter> adoDbParameters,
            CancellationToken cancellationToken)
        {
            var result = await this.ExecuteSqlQueryAsync<TModel>(
                sqlText, adoDbParameters, CommandBehavior.SingleRow, cancellationToken);

            return result.FirstOrDefault();
        }

        public object SelectOne(
            Type modelType,
            string sqlText,
            IList<IAdoDbParameter> adoDbParameters)
        {
            var result = this.ExecuteSqlQuery(
                modelType,
                sqlText,
                adoDbParameters,
                CommandBehavior.SingleRow);

            return ((IEnumerable<object>)result).Cast<object>().FirstOrDefault();
        }

        public async Task<object> SelectOneAsync(
            Type modelType,
            string sqlText,
            IList<IAdoDbParameter> adoDbParameters,
            CancellationToken cancellationToken)
        {
            var result = await this.ExecuteSqlQueryAsync(
                modelType,
                sqlText,
                adoDbParameters,
                CommandBehavior.SingleRow,
                cancellationToken);

            return ((IEnumerable<object>)result).Cast<object>().FirstOrDefault();
        }

        public TValue Scalar<TValue>(
            string sqlText,
            IList<IAdoDbParameter> adoDbParameters)
        {
            return _provider.ExecuteSqlScalar<TValue>(sqlText, adoDbParameters);
        }

        public Task<TValue> ScalarAsync<TValue>(
            string sqlText,
            IList<IAdoDbParameter> adoDbParameters,
            CancellationToken cancellationToken)
        {
            return _provider.ExecuteSqlScalarAsync<TValue>(sqlText, adoDbParameters, cancellationToken);
        }

        public object ExecuteSqlQuery(
            Type modelType,
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CommandBehavior commandBehavior = CommandBehavior.Default)
        {
            var methodInfo = this.GetType().GetMethod(
                            nameof(this.ExecuteSqlQuery),
                            BindingFlags.Instance | BindingFlags.Public, 
                            null,
                            new Type[] 
                            {
                                typeof(string),
                                typeof(IEnumerable<IAdoDbParameter>),
                                typeof(CommandBehavior) 
                            }, 
                            null);

            var bound = methodInfo.MakeGenericMethod(modelType);

            try
            {
                return bound.Invoke(this, new object[] { sqlText, parameters, commandBehavior });
            }
            catch (Exception e)
            {
                throw e.InnerException;
            }
        }

        public async Task<object> ExecuteSqlQueryAsync(
            Type modelType,
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CommandBehavior commandBehavior = CommandBehavior.Default,
            CancellationToken cancellationToken = default)
        {

            var methodInfo = this.GetType().GetMethod(
                            nameof(this.InternalExecuteSqlQueryAsync),
                            BindingFlags.Instance | BindingFlags.NonPublic, 
                            null,
                            new Type[] 
                            {
                                typeof(string),
                                typeof(IEnumerable<IAdoDbParameter>),
                                typeof(CommandBehavior),
                                typeof(CancellationToken)
                            },
                            null);

            var bound = methodInfo.MakeGenericMethod(modelType);

            return await (Task<object>)bound.Invoke(this, new object[] { sqlText, parameters, commandBehavior, cancellationToken });
          }

        public object ExecuteSqlQuery2(
            Type elementType,
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CommandBehavior commandBehavior = CommandBehavior.Default)
        {
            IAdoDbDataVisitor dataVisitor = null;
            var resultType = typeof(List<>).MakeGenericType(new Type[] { elementType });
            var result = (IList)Activator.CreateInstance(resultType, new object[] { });

            _context.CurrentConnection.Open();

            try
            {
                dataVisitor = _provider.ExecuteSqlQuery(
                                sqlText, parameters, commandBehavior);

                while (dataVisitor.MoveNext())
                {
                    var reader = dataVisitor.ReadData();
                    result.Add(Convert.ChangeType(reader.GetValue(0), elementType));
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dataVisitor != null)
                {
                    dataVisitor.Dispose();
                }

                _context.CurrentConnection.Close();
            }

            return result;
        }

        public async Task<object> ExecuteSqlQuery2Async(
            Type elementType,
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CommandBehavior commandBehavior = CommandBehavior.Default,
            CancellationToken cancellationToken = default)
        {
            IAdoDbDataVisitor dataVisitor = null;
            var resultType = typeof(List<>).MakeGenericType(new Type[] { elementType });
            var result = (IList)Activator.CreateInstance(resultType, new object[] { });

            await _context.CurrentConnection.OpenAsync(cancellationToken);

            try
            {
                dataVisitor = await _provider.ExecuteSqlQueryAsync(
                                sqlText, parameters, commandBehavior);

                return await this.ToListAsync(dataVisitor, cancellationToken, elementType);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dataVisitor != null)
                {
                    await dataVisitor.DisposeAsync();
                }

                await _context.CurrentConnection.CloseAsync();
            }
        }

        private async Task<IList> ToListAsync(
            IAdoDbDataVisitor dataVisitor, CancellationToken cancellationToken, Type elementType)
        {
            var resultType = typeof(List<>).MakeGenericType(new Type[] { elementType });
            var result = (IList)Activator.CreateInstance(resultType, new object[] { });

            while (!cancellationToken.IsCancellationRequested && 
                await dataVisitor.MoveNextAsync())
            {
                var reader = dataVisitor.ReadData();
                result.Add(Convert.ChangeType(reader.GetValue(0), elementType));
            }

            return result;
        }

        public IList<TModel> ExecuteSqlQuery<TModel>(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CommandBehavior commandBehavior = CommandBehavior.Default)
        {
            _context.CurrentConnection.Open();

            try
            {
                var queryResult = _provider.ExecuteSqlQuery<TModel>(
                    sqlText, parameters, commandBehavior);

                return queryResult.ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                _context.CurrentConnection.Close();
            }
        }

        public async Task<IList<TModel>> ExecuteSqlQueryAsync<TModel>(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CommandBehavior commandBehavior,
            CancellationToken cancellationToken)
        {
            await _context.CurrentConnection.OpenAsync(cancellationToken);

            try
            {
                var queryResult = await _provider.ExecuteSqlQueryAsync<TModel>(
                    sqlText, parameters, commandBehavior, cancellationToken);
                
                return await queryResult.ToListAsync(cancellationToken);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                await _context.CurrentConnection.CloseAsync();
            }
        }

        private async Task<object> InternalExecuteSqlQueryAsync<TModel>(
            string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            CommandBehavior commandBehavior,
            CancellationToken cancellationToken)
        {
            await _context.CurrentConnection.OpenAsync(cancellationToken);

            try
            {
                var queryResult = await _provider.ExecuteSqlQueryAsync<TModel>(
                    sqlText, parameters, commandBehavior, cancellationToken);
                
                return  await queryResult.ToListAsync(cancellationToken);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                await _context.CurrentConnection.CloseAsync();
            }
        }
    }
}
